package com.mbc.common.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;


import com.mbc.common.entities.LinkServer;
import com.mbc.common.data.LinkServerDataList;
import com.mbc.common.param.CriteriaParams;

public class LinkServerDAOImpls extends DAO<LinkServer> implements LinkServerDAO {

	public LinkServerDAOImpls(Connection conn)
	{
		this.conn = conn;
		this.table_name = "LinkServer";
	}

	@Override
	public void create(LinkServer entity) throws SQLException {
		query = "insert into " + table_name + " ( Link_Svr_Id, Server, DB_Name, Login_Id, Password, Link_Type, Server_Type, Instance, Port) values( ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
				pstmt.setLong(i++, entity.getLink_Svr_Id());
		pstmt.setString(i++, entity.getServer());
		pstmt.setString(i++, entity.getDB_Name());
		pstmt.setString(i++, entity.getLogin_Id());
		pstmt.setString(i++, entity.getPassword());
		pstmt.setInt(i++, entity.getLink_Type());
		pstmt.setByte(i++, entity.getServer_Type());
		pstmt.setString(i++, entity.getInstance());
		pstmt.setInt(i++, entity.getPort());

		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void update(LinkServer entity) throws SQLException {
		query = "update " + table_name + " set  Server = ?, DB_Name = ?, Login_Id = ?, Password = ?, Link_Type = ?, Server_Type = ?, Instance = ?, Port = ?";
		pstmt = conn.prepareStatement(query);
		int i = 1;
				pstmt.setString(i++, entity.getServer());
		pstmt.setString(i++, entity.getDB_Name());
		pstmt.setString(i++, entity.getLogin_Id());
		pstmt.setString(i++, entity.getPassword());
		pstmt.setInt(i++, entity.getLink_Type());
		pstmt.setByte(i++, entity.getServer_Type());
		pstmt.setString(i++, entity.getInstance());
		pstmt.setInt(i++, entity.getPort());

		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void delete(long key) throws SQLException {
		query = getDeleteQuery();
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public LinkServer find(long key) throws SQLException {
		LinkServer l_LinkServers = null;
		query = getSelectQuery();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next())
			l_LinkServers = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_LinkServers;
	}

	@Override
	public ArrayList<LinkServer> findAll() throws SQLException {
		ArrayList<LinkServer> l_LinkServersList = new ArrayList<LinkServer>();
		query = getSelectQuery();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			l_LinkServersList.add(readRecord(rs));
		}
		rs.close();
		pstmt.close();
		return l_LinkServersList;
	}

	@Override
	protected LinkServer readRecord(ResultSet rs) throws SQLException {
		LinkServer l_entity = new LinkServer();
				l_entity.setLink_Svr_Id(rs.getLong("Link_Svr_Id"));
		l_entity.setServer(rs.getString("Server"));
		l_entity.setDB_Name(rs.getString("DB_Name"));
		l_entity.setLogin_Id(rs.getString("Login_Id"));
		l_entity.setPassword(rs.getString("Password"));
		l_entity.setLink_Type(rs.getInt("Link_Type"));
		l_entity.setServer_Type(rs.getByte("Server_Type"));
		l_entity.setInstance(rs.getString("Instance"));
		l_entity.setPort(rs.getInt("Port"));

		return l_entity;
	}

	public LinkServerDataList getList(CriteriaParams cri) throws SQLException{
		LinkServerDataList l_DataList = new LinkServerDataList();
		ArrayList<LinkServer> l_entityList = new ArrayList<LinkServer>();
		String l_cri = "";
		query = "set rowcount " + cri.getSize() +
			" select * from (select row_number() over(order by Link_Svr_Id) as [rowno], * from " + table_name +
			" where 1 = 1" + l_cri + ") " + table_name +
			" where rowno > " + cri.getIndex();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next()) {
			do {	
				l_entityList.add(readRecord(rs));
			} while (rs.next());
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		query = "select count(Link_Svr_Id) from " + table_name + " where 1 = 1" + l_cri;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next()){
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
    }

	@Override
	public LinkServer findByLinkType(int type) throws SQLException {
		LinkServer l_LinkServers = null;
		query = getSelectQuery() + " where Link_Type = " + type;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next())
			l_LinkServers = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_LinkServers;
	}
}
